﻿Imports System.Data.OleDb
Public Class Voorbeeld
    Inherits System.Web.UI.Page
    Dim intArrayItem As Integer
    Dim strWelkeVergadering As String
    Dim strGeselecteerdeId() As String
    Dim intVergaderingId As Integer

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If IsPostBack = False Then
            Dim cnndb3 As New OleDbConnection
            Dim cmdCommando As New OleDbCommand
            Dim dtrDataReader As OleDbDataReader
            Dim strsqlEmailadress As String
            Dim intAantalGeselecteerde As Integer
            Dim strEmail() As String
            VergaderingIdProperty.Text = PreviousPage.IdVergadering
            intVergaderingId = PreviousPage.IdVergadering
            intAantalGeselecteerde = PreviousPage.AantalGeselecteerd
            strGeselecteerdeId = PreviousPage.GeselecteerdeId
            AantalGeselecteerdProperty.Text = PreviousPage.AantalGeselecteerd
            TitelVergaderingProperty.Text = PreviousPage.TitelVergadering

            For intTeller As Integer = 0 To intAantalGeselecteerde
                strsqlEmailadress = "SELECT [E-mail] FROM [tblAandeelhouders] WHERE [Id] = " & strGeselecteerdeId(intTeller)


                'Connectie voorbereiden
                cnndb3.ConnectionString = My.Settings.csAandeelhouders
                cmdCommando.Connection = cnndb3
                cmdCommando.CommandText = strsqlEmailadress
                Try
                    'Openen van database
                    cnndb3.Open()
                Catch ex As Exception
                    lblFout.Text = "Kan de databank niet openen"
                End Try
                Try
                    'Gegevens uit de database halen
                    dtrDataReader = cmdCommando.ExecuteReader
                    Do While dtrDataReader.Read()

                        ReDim Preserve strEmail(intTeller)
                        lstEmails.Items.Add(New ListItem(dtrDataReader.Item("E-mail"), strGeselecteerdeId(intTeller)))
                    Loop
                Catch ex As Exception
                    lblFout.Text = "Kan de gegevens niet uit de databank halen"
                Finally
                    'Connectie sluiten met de database
                    cnndb3.Close()

                End Try
            Next
            MailcontentPropertyPagina.InnerHtml = PreviousPage.DefaultBrief
            mailContent.InnerHtml = PreviousPage.DefaultBrief
            myReplace()
            strWelkeVergadering = PreviousPage.TitelVergadering
            txtVergadering.Text = strWelkeVergadering


        End If



    End Sub

    Protected Sub btnVerzenden_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnVerzenden.Click
        pnlZeker.Visible = True
        btnVerzenden.Visible = False
    End Sub

    Protected Sub btnVersturen_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnVersturen.Click
        Server.Transfer("Verzenden2.aspx")
    End Sub

    Protected Sub btnNaarVerzenden_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnNaarVerzenden.Click
        Server.Transfer("Verzenden.aspx")
    End Sub

    Protected Sub btnNaarVerzenden2_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnNaarVerzenden2.Click
        Server.Transfer("Verzenden2.aspx")
    End Sub
    Public Function myReplace() As Boolean
        'Declaratie
        Static strDatum1 As String = "[$date1$]"
        Static strDatum2 As String = "[$date2$]"
        Static strPlaats As String = "[$plaats$]"
        Static strDefaultAgendaPunt As String = "[Agendapunten]"
        Dim strDefaultTitel As String = "[Titel]"
        Dim strGoeiVormAgendaPunten As String
        Dim strDatum1Db As String
        Dim strDatum2Db As String
        Dim strPlaatsDb As String
        Dim cnndb3 As New OleDbConnection
        Dim cmdCommando As New OleDbCommand
        Dim dtrDataReader2 As OleDbDataReader
        Dim strsqlEmailadress As String
        Dim strGuid As String
        Dim strsqlGuid As String
        Dim strGekozenVergadering As String = PreviousPage.TitelVergadering
        Dim strsqlAgendaPunten As String
        Dim strAgendaPunten() As String
        Dim intTellerAgendaPunt As Integer = -1

        'SQL's klaarzetten 
        strsqlAgendaPunten = "SELECT [Omschrijving] FROM [tblAgendaPunten] WHERE [Vergadering] =" & intVergaderingId
        strsqlGuid = "SELECT [Guid] FROM [tblAandeelhouders] WHERE [Id]=" & strGeselecteerdeId(0)
        strsqlEmailadress = "SELECT [Start], [Eind], [Plaats] FROM [tblVergaderingen] WHERE [Titel] ='" & strGekozenVergadering & "'"
        'Connectie klaarmaken
        cnndb3.ConnectionString = My.Settings.csAandeelhouders
        cmdCommando.Connection = cnndb3

        Try
            'Connectie openen
            cnndb3.Open()
        Catch ex As Exception
            lblFout.Text = "Kan de database niet openen"
        End Try
        Try
            'datareader klaarmaken
            dtrDataReader2 = cmdCommando.ExecuteReader
            'Gegevens uit databank uithalen
            Do While dtrDataReader2.Read()
                intVergaderingId = dtrDataReader2.Item("Id")
            Loop
        Catch ex As Exception
            lblFout.Text = "Er zijn geen agendapunten of de gegevens konden niet uit de databank gehaald worden"
        Finally
            'Connectie sluiten
            cnndb3.Close()
        End Try





        cmdCommando.CommandText = strsqlEmailadress
        Try
            'Connectie openen
            cnndb3.Open()
        Catch ex As Exception
            lblFout.Text = "Kan de database niet openen"
        End Try
        Try
            'datareader klaarmaken
            dtrDataReader2 = cmdCommando.ExecuteReader
            'Gegevens uit databank uithalen
            Do While dtrDataReader2.Read()
                strDatum1Db = dtrDataReader2.Item("Start")
                strDatum2Db = dtrDataReader2.Item("Eind")
                strPlaatsDb = dtrDataReader2.Item("Plaats")

            Loop
        Catch ex As Exception
            lblFout.Text = "Kan de gegevens niet uit de database halen"
        Finally
            'Connectie sluiten
            cnndb3.Close()
        End Try


        'Andere SQL in command steken
        cmdCommando.CommandText = strsqlGuid
        Try
            'Connectie openen
            cnndb3.Open()
        Catch ex As Exception
            lblFout.Text = "Kan de database niet openen"
        End Try
        Try
            dtrDataReader2 = cmdCommando.ExecuteReader
            'Gegevens uit databank halen
            Do While dtrDataReader2.Read()
                strGuid = dtrDataReader2.Item("Guid").ToString()
            Loop
        Catch ex As Exception
            lblFout.Text = "Kan de gegevens niet ophalen uit de database"
        Finally
            'Connectie sluiten
            cnndb3.Close()
        End Try


        'Andere SQL in command steken
        cmdCommando.CommandText = strsqlAgendaPunten
        Try
            'Connectie openen
            cnndb3.Open()
        Catch ex As Exception
            lblFout.Text = "Kan de databank niet openen"
        End Try
        Try
            'Gegevens uit databank halen
            Do While dtrDataReader2.Read()
                intTellerAgendaPunt += 1
                ReDim Preserve strAgendaPunten(intTellerAgendaPunt)
                strAgendaPunten(intTellerAgendaPunt) = dtrDataReader2.Item("Omschrijving")
            Loop
        Catch ex As Exception
            lblFout.Text = "Kan de gegevens in databank niet ophalen"
            ReDim Preserve strAgendaPunten(0)
            strAgendaPunten(0) = "Geen"
        Finally
            'Connectie sluiten
            cnndb3.Close()
        End Try


        If IsNothing(dtrDataReader2) Then
            dtrDataReader2.Close()
        End If
        'Agendapunten klaarmaken om in de brief te steken ( goeie vorm)
        If Not strAgendaPunten(0) = "Geen" Then
            For intTeller As Integer = 0 To intTellerAgendaPunt
                If intTeller = 0 Then
                    strGoeiVormAgendaPunten &= "<ul> <li> " & strAgendaPunten(intTeller) & "</li> </ul>"
                ElseIf intTeller = intTellerAgendaPunt Then

                    strGoeiVormAgendaPunten &= "<li> " & strAgendaPunten(intTeller) & "</li>"
                Else
                    strGoeiVormAgendaPunten &= "<li> " & strAgendaPunten(intTeller) & "</li> </ul>"
                End If

            Next
        End If
        'Alles vervangen om in de brief te steken
        mailContent.InnerHtml = mailContent.InnerHtml.Replace(strDatum1, strDatum1Db)
        mailContent.InnerHtml = mailContent.InnerHtml.Replace(strDatum2, strDatum2Db)
        mailContent.InnerHtml = mailContent.InnerHtml.Replace(strPlaats, strPlaatsDb)
        mailContent.InnerHtml = mailContent.InnerHtml.Replace("[uniqu]", "<a href=Bevestiging.aspx?AH=" & strGuid & ">Klik hier voor aanwezig of afwezig</a>")
        If strAgendaPunten(0) = "Geen" Then
            mailContent.InnerHtml = mailContent.InnerHtml.Replace(strDefaultAgendaPunt, "(Geen)")
        Else
            mailContent.InnerHtml = mailContent.InnerHtml.Replace(strDefaultAgendaPunt, strGoeiVormAgendaPunten)

        End If
        mailContent.InnerHtml = mailContent.InnerHtml.Replace(strDefaultTitel, strGekozenVergadering)

        Return True

    End Function
    Public ReadOnly Property GeselecteerdeId() As String()
        Get
            Dim strgeselecteerdeIdInProperty() As String
            For intteller As Integer = 0 To lstEmails.Items.Count - 1
                ReDim Preserve strgeselecteerdeIdInProperty(intteller)
                strgeselecteerdeIdInProperty(intteller) = lstEmails.Items(intteller).Value
            Next
            Return strgeselecteerdeIdInProperty
        End Get
    End Property


    Public ReadOnly Property AantalGeselecteerd As Integer
        Get
            Return AantalGeselecteerdProperty.Text
        End Get
    End Property

    Public ReadOnly Property DefaultBrief As String
        Get
            Return MailcontentPropertyPagina.InnerHtml
        End Get
    End Property


    Public ReadOnly Property IdVergadering As String
        Get
            Return VergaderingIdProperty.Text
        End Get
    End Property
    Public ReadOnly Property TitelVergadering As String
        Get
            Return TitelVergaderingProperty.Text
        End Get
    End Property


End Class